# 1.基础条件查询
# 1 基本查询
# get 查询单一结果DoesNotExist异常。
from book.models import BookInfo, PeopleInfo
BookInfo.objects.get(id=1)
BookInfo.objects.get(id=5)
BookInfo.objects.all()
BookInfo.objects.count()
PeopleInfo.objects.count()
# 2 过滤查询
"""
实现SQL中的where功能, 包括:
    - filter过滤出多个结果
    - exclude排除掉符合条件剩下的结果
    - get过滤单一结果
对于过滤条件的使用, 上述三个方法相同, 估仅以filter进行讲解。

过滤条件的表达语法如下：
     属性名称__比较运算符=值
     属性名称和比较运算符见使用两个下划线， 所以属性名不能包括多个下划线
"""

BookInfo.objects.get(id__exact=1)
BookInfo.objects.get(id=1)
BookInfo.objects.get(pk=1)
BookInfo.objects.filter(name__contains='传')
BookInfo.objects.filter(name__endswith='部')
BookInfo.objects.filter(name__startswith='射')
BookInfo.objects.filter(name__isnull='True')
BookInfo.objects.filter(name__isnull='False')
BookInfo.objects.filter(id__in=[1, 3, 5])
"""
    gt大于 (greater then)
    gte大于等于 (greater then equal)
    lt小于 (less then)
    lte小于等于 (less then equal)
"""
BookInfo.objects.filter(id__gt=3)
BookInfo.objects.filter(id__lte=2)
BookInfo.objects.exclude(id=3)
BookInfo.objects.filter(pub_date__year=1980)
BookInfo.objects.filter(pub_date__gt='1980-1-1')
"""
1. F对象
     之前的查询都是对象属性与常量值比较， 两个属性怎么比较呢？ 答：使用F对象，被定义在django.db.models中。

语法如下：
    F(属性名)
例： 查询阅读量大于等于评论量的图书。
"""
# 导包
from django.db.models import F, Q
BookInfo.objects.filter(readcount__gte=F('commentcount'))
BookInfo.objects.filter(readcount__gte=F('commentcount')*2)
"""
2. Q对象
     多个过滤器逐个调用表示逻辑与关系， 同sql语句中where部分的and关键字。
"""
BookInfo.objects.filter(readcount__gt=20, id__lt=3)
BookInfo.objects.filter(Q(readcount__gt=20) | Q(id__lt=3))
BookInfo.objects.filter(~Q(id=3))
BookInfo.objects.filter(~Q(id__in=[1, 3]))
from django.db.models import Sum, Max, Avg, Count, Min
BookInfo.objects.aggregate(Sum('readcount'))
BookInfo.objects.aggregate(Max('commentcount'))
BookInfo.objects.aggregate(Avg('readcount'))


BookInfo.objects.all()
BookInfo.objects.all().order_by('readcount')

BookInfo.objects.all().order_by('-commentcount')
BookInfo.objects.all().order_by('pub_date')
BookInfo.objects.all().order_by('-pub_date')
book = BookInfo.objects.get(id=1)

book.peopleinfo_set.all()
book = BookInfo.objects.get(name='雪山飞狐')
book.peopleinfo_set.all()
people = PeopleInfo.objects.get(id=1)
people.book
people.book_id
people = PeopleInfo.objects.get(name='虚竹')
people.book
book = BookInfo.objects.get(peopleinfo__name='郭靖')
books = BookInfo.objects.filter(peopleinfo__description__contains='八')
peoples = PeopleInfo.objects.filter(book__name='天龙八部')
peoples = PeopleInfo.objects.filter(book__readcount__gt=30)

